#!/usr/bin/env python
# encoding: utf-8
"""
Q&GFormatter2.py

Created by Brant Faircloth on 2008-01-10.
Copyright (c) 2008 Brant C. Faircloth. All rights reserved.
"""

import MySQLdb, csv, pdb

class excelSingleSpace:
    """class for csv module to work correctly"""
    delimiter = ','
    quotechar = '"'
    escapechar = None
    doublequote = True
    skipinitialspace = False
    lineterminator = '\r'

def genData(c,y,handle):
    '''get the data'''
    #pdb.set_trace()
    # get bird_id and covey_id from ttrs_covey_members for a specific year
    query = (("SELECT bird_id, covey_id FROM ttrs_covey_members WHERE year = '%s' and transient != 1") % (y))
    c.execute(query)
    bird = c.fetchall()
    handle.write('bird_id\tgroup\tP1A7\tP1F3\tP1H12\tP2D7\tPA12G\tPA1C\tPA1F\tPA3E\tPA3F\tPA3G\tPA5F\tPBA4\tPBH5\tPCF5\r\n')
    for b in bird:
        iden,covey = b
        query = (("SELECT P1A7A,P1A7B,P1F3A,P1F3B,P1H12A,P1H12B,P2D7A,P2D7B,PA12GA,PA12GB,PA1CA,PA1CB,PA1FA,PA1FB,PA3EA,PA3EB,PA3FA,PA3FB,PA3GA,PA3GB,PA5FA,PA5FB,PBA4A,PBA4B,PBH5A,PBH5B,PCF5A,PCF5B from ttrs_genotypes where bird_id = '%s'") %  (iden))
        #pdb.set_trace()
        c.execute(query)
        d = c.fetchall()
        if d == ():
            pass
        else:
            # convert 'NULL' and ' NULL' to "*"
            cleanGen = ()
            genPass = 0
            for allele in d[0]:
                if allele == 'NULL' or allele == ' NULL':
                    #pdb.set_trace()
                    cleanGen += ('*',)
                    genPass += 1
                else:
                    cleanGen += (allele,)
            if genPass <= 14:
                handle.write(("%s\t%s") % (iden,covey))
                for allele in range(0,len(cleanGen),2):
                    if cleanGen[allele] == '*' or cleanGen[allele+1] == '*':
                        dots = "\t•/•"
                        u = unicode(dots, "utf-8" )
                        handle.write(u.encode('mac-roman'))
                    else:
                        handle.write(('\t%s/%s') % (cleanGen[allele],cleanGen[allele+1]))
                handle.write('\r\n')


def main():
    database = MySQLdb.connect("parameters")
    c = database.cursor()
    year = [2001,2002,2003]
    #year = [2001]
    for y in year:
        filename = (('%s_covey_pairwise.txt') % (y))
        handle = open(filename,'w')
        genData(c,y,handle)
        handle.close()
    c.close()

if __name__ == '__main__':
    main()